package cn.com.callback

import cn.com.elite.EliteBean
import org.slf4j.Logger
import org.slf4j.LoggerFactory
import org.springframework.jdbc.core.JdbcTemplate
import org.springframework.jdbc.support.rowset.SqlRowSet

public class autoReceptionMonitor implements EliteThreadInvoke {
    private static final Logger log = LoggerFactory.getLogger(autoReceptionMonitor.class);

    @Override
    public int doCallBack(EliteBean eliteBean, String s) {
        long startTime = System.currentTimeMillis();
        // 按天,小时,坐席分组
        log.debug("3Vjia在我的接待页面统计定时任务启动任务content:" + s);
        try {
            String sql = '''
Select 
		   C1.Agent_Id, -- 坐席id
       C1.Date_Day, -- 日期F
       C1.Date_Hour, -- 小时
       If(Ccm.Staff_Yxjd_Num, Ccm.Staff_Yxjd_Num, 0) As Staff_Yxjd_Num, -- 人工有效接待量
       If(Ccm.Total_Jd_Time, Ccm.Total_Jd_Time, 0) As Total_Jd_Time, -- 总接待时间
       If(Ccm.Total_Times, Ccm.Total_Times, 0) As Total_Times, -- 总接待次数
       If(Cush.Time, Cush.Time, 0) As Time, -- 在线时长 计算
       If(Cm.Answer_Time, Cm.Answer_Time, 0) As Answer_Time, -- 响应总时长
       If(Cm.Answer_Times, Cm.Answer_Times, 0) As Answer_Times, -- 响应总次数
       If(Cs.Good_Times, Cs.Good_Times, 0) As Good_Times, -- 好评次数
       If(Cs.Rate_Times, Cs.Rate_Times, 0) As Rate_Times, -- 评价次数
       If(Cs.Total_Rate, Cs.Total_Rate, 0) As Total_Rate, -- 评价总分数
       If(Cs.Total_Session_Num, Cs.Total_Session_Num, 0) As Total_Session_Num  , -- 总会话数
			 IFNULL(e.访客总回复数,0) as custtotal
From (Select Distinct Aq.Agent_Id, Ct.Date_Day, Ct.Date_Hour
          From ((Select Distinct Agent_Id From Agent_Queue) Aq Left Join
                (Select Staff_Id From Chat_Login_His Where Logintime >= Concat(Date_Format(Now(), '%Y-%m-%d'), ' 00:00:00') Group By Staff_Id) Lh On
                Lh.Staff_Id = Aq.Agent_Id Left Join
                (Select To_Chat_User_Id,
                        From_Unixtime(Creation_Time / 1000, '%Y-%m-%d') As Date_Day,
                        From_Unixtime(Creation_Time / 1000, '%H') As Date_Hour
                   From Chat_Request Cr
                  Where 1 = 1
                    And Creation_Time > Replace(Unix_Timestamp(Date_Add(Now(), Interval - 1 Day)), '.', '') * 1000
                  Group By From_Unixtime(Cr.Creation_Time / 1000, '%Y-%m-%d'), From_Unixtime(Cr.Creation_Time / 1000, '%H'),cr.to_chat_user_id) Ct On 1 = 1)
         Where Lh.Staff_Id Is Not Null
				 
				 ) C1
  Left Join (Select Cr.To_Chat_User_Id,
                    From_Unixtime(Cr.Creation_Time / 1000, '%Y-%m-%d') As Date_Day,
                    From_Unixtime(Cr.Creation_Time / 1000, '%H') As Date_Hour,
                    Sum(Case
                          When LENGTH(to_chat_user_id)=6 && to_chat_user_id not like 'BOT%' and  c1.yxtotal>= 1 Then
                           1
                          Else
                           0
                        End) As Staff_Yxjd_Num, -- 人工有效接待量
                    Sum(Case
                          When ((Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%') Or (Cr.Request_Type_Id = 3) Or
                               Cr.Request_Type_Id = 1) And Cr.Request_Status_Id = 1 Then
                           Cr.Agent_End_Time - Cr.End_Time
                          Else
                           0
                        End) As Total_Jd_Time, -- 总接待时间
                    Sum(Case
                          When ((Cr.Request_Type_Id = 4 And Cr.From_Chat_User_Id Like 'BOT%') Or (Cr.Request_Type_Id = 3) Or
                               Cr.Request_Type_Id = 1) And Cr.Request_Status_Id = 1 Then
                           1
                          Else
                           0
                        End) As Total_Times -- 总接待次数
               From Chat_Request Cr
               Left Join (Select  cm.Chat_Session_Id ,  
								count( case when Length( cm.Chat_User_Id )= 6 and cm.reply_delay>0 then cm.id else null end ) as yxtotal
              From Chat_Message cm
			left join chat_request cr on cr.chat_session_id=cm.chat_session_id  and to_chat_user_id is not null  and (from_chat_user_id like '%BOT000%' or length(from_chat_user_id)=36)
			Left Join Chat_Session Cs  On Cs.Id = Cr.Chat_Session_Id  
             	where Cr.Creation_Time > Replace(Unix_Timestamp(Date_Add(Now(), Interval - 1 Day)), '.', '') * 1000
              Group By cm.Chat_Session_Id ) C1
                 On Cr.Chat_Session_Id = C1.Chat_Session_Id
              Where 1 = 1
                And Cr.Creation_Time > Replace(Unix_Timestamp(Date_Add(Now(), Interval - 1 Day)), '.', '') * 1000
              Group By Cr.To_Chat_User_Id, From_Unixtime(Cr.Creation_Time / 1000, '%Y-%m-%d'), From_Unixtime(Cr.Creation_Time / 1000, '%H')) Ccm
    On Ccm.To_Chat_User_Id = C1.Agent_Id
   And C1.Date_Day = Ccm.Date_Day
   And C1.Date_Hour = Ccm.Date_Hour
              Left Join (select to_chat_user_id Last_Agent_Id,Date_Day,Date_Hour,count(1) Total_Session_Num,count(人工服务评价数) Rate_Times,                          sum(人工服务满意度得分) Total_Rate 
                            ,count(人工服务五分) Good_Times
                             from (select to_chat_user_id,From_Unixtime(Start_Time / 1000, '%Y-%m-%d') As Date_Day, -- 日期
                                                From_Unixtime(Start_Time / 1000, '%H') As Date_Hour -- 小时\t
                            ,if(@order_num=cr.chat_session_id,@rownum:=@rownum+1,@rownum:=1) as nums
                            , @rownum=1
                            ,case when cs.rating_type_id is not null and @rownum=1 then 1 else null end as 人工服务评价数
                            ,case when cs.rating_id is not null and @rownum=1 then cs.rating_id else null end  as 人工服务满意度得分
                            ,case when cs.rating_id=5 and @rownum=1 then 1 else null end as 人工服务五分 
            
            
            
                            ,@order_num:=cr.chat_session_id,@rownum
                            from (select @rownum :=1 , @order_num := null ,@rank1:=0) a,chat_request cr
                            left join chat_session  cs on cs.id=cr.chat_session_id 
                            
                             where (request_type_id=3 or request_type_id=4 or (cr.request_type_id=1 and cr.to_chat_user_id  not in (select bot_name from robot))) and request_status_id = 1 
                             And creation_time > Replace(Unix_Timestamp(Date_Add(now(), Interval - 1 Day)), '.', '') * 1000
                            
                            order by chat_session_id,creation_time desc) a
                            left join staff s on s.staff_id=a.to_chat_user_id 
                            where nums=1 
                            group by to_chat_user_id,Date_Day,Date_Hour) Cs
    On C1.Agent_Id = Cs.Last_Agent_Id
   And C1.Date_Day = Cs.Date_Day
   And C1.Date_Hour = Cs.Date_Hour
  Left Join (Select Staff_Id,
                    From_Unixtime(Starttime / 1000, '%Y-%m-%d') As Date_Day,
                    From_Unixtime(Starttime / 1000, '%H') As Date_Hour,
                    Sum(Case
                          When ifnull(Endtime,UNIX_TIMESTAMP(now())*1000) - Starttime > 0 Then
                           ifnull(Endtime,UNIX_TIMESTAMP(now())*1000) - Starttime
                          Else
                           0
                        End) As Time -- 在线时长
               From Chat_User_Status_His
              Where Status = 1
                And Starttime > Replace(Unix_Timestamp(Date_Add(Now(), Interval - 1 Day)), '.', '') * 1000
              Group By Staff_Id, From_Unixtime(Starttime / 1000, '%Y-%m-%d'), From_Unixtime(Starttime / 1000, '%H')) Cush
    On C1.Agent_Id = Cush.Staff_Id
   And C1.Date_Day = Cush.Date_Day
   And C1.Date_Hour = Cush.Date_Hour
  Left Join (
	
	Select Chat_User_Id,
                    From_Unixtime(Post_Time / 1000, '%Y-%m-%d') As Date_Day,
                    From_Unixtime(Post_Time / 1000, '%H') As Date_Hour,
                    Sum(Reply_Delay) As Answer_Time, -- 响应总时长
                    Count(*) As Answer_Times -- 响应总次数
               From Chat_Message
              Where Reply_Delay > 0
                And Post_Time > Replace(Unix_Timestamp(Date_Add(Now(), Interval - 1 Day)), '.', '') * 1000 
              Group By Chat_User_Id, From_Unixtime(Post_Time / 1000, '%Y-%m-%d'), From_Unixtime(Post_Time / 1000, '%H') 
							
							) Cm
    On C1.Agent_Id = Cm.Chat_User_Id
   And C1.Date_Day = Cm.Date_Day
   And C1.Date_Hour = Cm.Date_Hour
left join (
	select
		count( DISTINCT case when LENGTH( c1.chat_user_id )= 36 then c1.id else null end ) as 访客总回复数,
		c2.chat_user_id as staffid,
		
		From_Unixtime(c1.Post_Time / 1000, '%Y-%m-%d') As Date_Day,
		From_Unixtime(c1.Post_Time / 1000, '%H') As Date_Hour 
	from
		chat_message c1
		left join chat_message c2 on c1.chat_session_id = c2.chat_session_id 
	where
		c1.Post_Time > Replace ( Unix_Timestamp( Date_Add( Now(), Interval - 1 Day )), '.', '' ) * 1000 
		and LENGTH( c2.chat_user_id )= 6  
	group by
		c2.chat_user_id,
 		From_Unixtime(c1.Post_Time / 1000, '%Y-%m-%d') ,
		From_Unixtime(c1.Post_Time / 1000, '%H')
		
	HAVING
		访客总回复数 > 0   
		ORDER BY 2,3,4 desc
	) e 
   On C1.Agent_Id = e.staffid
   And C1.Date_Day = e.Date_Day
   And C1.Date_Hour = e.Date_Hour
	 LEFT JOIN staff s on s.staff_id=c1.agent_id
	
	Where C1.Date_Day = Date_Format(Now(), '%Y-%m-%d')
	 

                    '''
            SqlRowSet selectList = eliteBean.chaxun_sqlRowSet(sql, "dataSource");
            log.debug("3Vjia我的接待页面统计定时任务查询返回-耗时：" + (System.currentTimeMillis() - startTime) + "【结果List】：" + selectList.toString());

            String upSql = "";
            String insertSql = "";

            List<String> SQLList = new ArrayList<String>();
            int k = 0;
            int j = 0;
            if (selectList != null) {
                while (selectList.next()) {
                    String staff_id = selectList.getString("Agent_Id");
                    String date_day = selectList.getString("Date_Day");
                    String date_hour = selectList.getString("Date_Hour");
                    String staff_void_num = selectList.getString("Staff_Yxjd_Num");
                    String total_jd_time = selectList.getString("Total_Jd_Time");
                    String total_times = selectList.getString("Total_Times");
                    String time = selectList.getString("Time");
                    String answer_time = selectList.getString("Answer_Time");
                    String answer_times = selectList.getString("Answer_Times");
                    String good_times = selectList.getString("Good_Times");
                    String rate_times = selectList.getString("Rate_Times");
                    String total_rate = selectList.getString("Total_Rate");
                    String total_session_num = selectList.getString("Total_Session_Num");
                    String custtotal = selectList.getString("custtotal");
                    // 判断该时段是否有数据
                    sql = " select id from 3vj_staff_session_monitor where staff_id = '" + staff_id + "' and  monitor_date = '" + date_day + "' and monitor_hour = '" + date_hour + "' ";
                    List resultList = eliteBean.chaxun_sql(sql, 'dataSource');

                    if (resultList.size() > 0) {
                        // 更新该时段数据
                        upSql = "UPDATE 3vj_staff_session_monitor " +
                                "       SET staff_valid_num = '" + staff_void_num + "'," +
                                "       total_reception_time = '" + total_jd_time + "'," +
                                "       total_reception_times = '" + total_times + "'," +
                                "       staff_online_time = '" + time + "'," +
                                "       staff_answer_time = '" + answer_time + "'," +
                                "       staff_answer_times = '" + answer_times + "'," +
                                "       good_rate_times = '" + good_times + "'," +
                                "       rate_times = '" + rate_times + "'," +
                                "       gross_score = '" + total_rate + "'," +
                                "       total_session = '" + total_session_num + "'," +
                                "       custtotal = '" + custtotal + "'," +
                                "       monitor_time = now() " +
                                "       WHERE monitor_date =  '" + date_day + "'" +
                                "           and monitor_hour = '" + date_hour + "'" +
                                "           and staff_id = '" + staff_id + "'";
                        SQLList.add(upSql);
                    } else {
                        // 添加数据
                        insertSql = "INSERT INTO 3vj_staff_session_monitor (" +
                                "  id," +
                                "  staff_id," +
                                "  staff_valid_num," +
                                "  total_reception_time," +
                                "  total_reception_times," +
                                "  staff_online_time," +
                                "  staff_answer_time," +
                                "  staff_answer_times," +
                                "  good_rate_times," +
                                "  rate_times," +
                                "  gross_score," +
                                "  total_session," +
                                "  monitor_date," +
                                "  monitor_hour," +
                                "  custtotal," +
                                "  monitor_time" +
                                "  )" +
                                "  VALUES" +
                                "  (" +
                                "  uuid()," +
                                "  '" + staff_id + "'," +
                                "  '" + staff_void_num + "'," +
                                "  '" + total_jd_time + "'," +
                                "  '" + total_times + "'," +
                                "  '" + time + "'," +
                                "  '" + answer_time + "'," +
                                "  '" + answer_times + "'," +
                                "  '" + good_times + "'," +
                                "  '" + rate_times + "'," +
                                "  '" + total_rate + "'," +
                                "  '" + total_session_num + "'," +
                                "  '" + date_day + "'," +
                                "  '" + date_hour + "'," +
                                "  '" + custtotal + "'," +
                                "  now())";
                        SQLList.add(insertSql);
                    }
                    k = k + 1;
                    j++;
                    if (k == 100) {
                        eliteBean.updateBatch(SQLList.toArray(new String[SQLList.size()]), "dataSource");
                        SQLList.clear();
                        k = 0;
                    }
                }
            }
            if (k > 0 && k < 100) {
                eliteBean.updateBatch(SQLList.toArray(new String[SQLList.size()]), "dataSource");
            }

            if (j > 0) {
                log.debug("3Vjia我的接待页面统计定时任务处理End...数量:" + j);
            } else {
                log.warn("3Vjia我的接待页面统计定时任务处理End...SQL空");
            }
            online_time(eliteBean);
            log.debug("3Vjia我的接待页面统计定时任务处理结束-总耗时:" + (System.currentTimeMillis() - startTime));
            return 1;

        } catch (Exception e) {
            log.error("3Vjia在我的接待页面统计定时任务操作异常", e);
            return 0;
        }

    }

    public void online_time(EliteBean eliteBean) {
        String sql = "";
        sql = "Select  Staff_Id," +
                " From_Unixtime(Starttime / 1000, '%Y-%m-%d') As Date_Day," +
                " From_Unixtime(Starttime / 1000, '%H') As Date_Hour," +
                " Sum(Case" +
                " When Endtime - Starttime > 0 Then" +
                " Endtime - Starttime" +
                " Else" +
                " 0" +
                " End) As Time" +
                " From Chat_User_Status_His" +
                " Where Status = 1" +
                " And Starttime > Replace(Unix_Timestamp(Date_Add(Now(), Interval - 1 Day)), '.', '') * 1000" +
                " Group By Staff_Id, From_Unixtime(Starttime / 1000, '%Y-%m-%d'), From_Unixtime(Starttime / 1000, '%H')";
        List arr = eliteBean.chaxun_sql(sql, "dataSource");
        log.debug("坐席在线时长：" + arr.toString());
        for (int a = 0; a < arr.size(); a++) {
            Map selectList = arr.get(a);
            String staff_id = selectList.get("Staff_Id").toString();
            String date_day = selectList.get("Date_Day").toString();
            String date_hour = selectList.get("Date_Hour").toString();
            String Time = selectList.get("Time").toString();
            sql = " select id from 3vj_staff_session_monitor where staff_id = '" + staff_id + "' and  monitor_date = '" + date_day + "' and monitor_hour = '" + date_hour + "' ";
            List resultList = eliteBean.chaxun_sql(sql, 'dataSource');
            log.debug("监控表是否存在：" + (resultList.size() > 0) );
            if (resultList.size() > 0) {
                // 更新该时段数据
                sql = "UPDATE 3vj_staff_session_monitor  set" +
                        "       staff_online_time = '"+Time+"', " +
                        "       monitor_time = now() " +
                        "       WHERE monitor_date =  '" + date_day + "'" +
                        "           and monitor_hour = '" + date_hour + "'" +
                        "           and staff_id = '" + staff_id + "'";
                log.debug("更新"+staff_id+"的"+date_day+" "+date_hour+"时的"+ eliteBean.update(sql, 'dataSource')+"数据");
            } else {
                // 添加数据
                sql = "INSERT INTO 3vj_staff_session_monitor (" +
                        "  id," +
                        "  staff_id," +
                        "  staff_online_time," +
                        "  monitor_date," +
                        "  monitor_hour," +
                        "  monitor_time" +
                        "  )" +
                        "  VALUES" +
                        "  (" +
                        "  uuid()," +
                        "  '" + staff_id + "'," +
                        "  '" + Time + "'," +
                        "  '" + date_day + "'," +
                        "  '" + date_hour + "'," +
                        "  now())";
                log.debug(date_day+" "+date_hour+"时添加"+staff_id+"的"+ eliteBean.update(sql, 'dataSource')+"数据");

            }
        }
    }


}
